﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using DataAccessLayer.Util;

namespace DataAccessLayer
{
    public class DataAccessManager
    {
        private static string connStr = ConfigurationManager.ConnectionStrings["NUSNETConnectionString"].ToString();

        private static string SEL_CUST = "SELECT UserName, UserPwd, Version,Counter,Enable FROM LOS.dbo.Holders";

        public static SqlConnection getConnection()
        {
            return new SqlConnection(connStr);
        }

        public static bool isEnabled(string UserName)
        {
            bool myStatus = true;
            string CHECKBLOCK = "SELECT UserName, Enable FROM LOS.dbo.Holders WHERE UserName = '" + UserName + "'";
            SqlConnection conn = getConnection();
            SqlCommand command = new SqlCommand(CHECKBLOCK, conn);
            SqlDataReader Dr;
            try
            {
                conn.Open();
                Dr = command.ExecuteReader();
                Dr.Read();
                myStatus = (Boolean)Dr["Enable"];
            }
            finally
            {
                conn.Close();
            }
            return myStatus;
        }

        public static bool ValidateUser(string UserName, string Password)
        {

            SqlConnection conn = getConnection();
            SqlCommand command = new SqlCommand(SEL_CUST, conn);
            SqlDataReader Dr;

            try
            {
                conn.Open();
                Dr = command.ExecuteReader();
                while (Dr.Read())
                {
                    string a;
                    a = Dr["UserPwd"].ToString();


                    if (UserName == (Dr["UserName"].ToString().Trim()) && (SecurityUtil.EncodePassword(Password) == Dr["UserPwd"].ToString()) && (Boolean)Dr["Enable"] == true)
                    {
                        return true;
                    }
                    else
                    {
                        DataAccessManager.EditCounter(UserName, (int)Dr["Counter"] + 1);
                        if ((int)Dr["Counter"] >= 2)
                            BlockUser(UserName);
                    }


                }
                Dr.Close();
            }
            finally
            {
                conn.Close();
            }

            return false;
        }

        private static void EditCounter(string UserName, int loginCounter)
        {
            string UPDATE_COUNTER = "UPDATE LOS.dbo.Holders SET Counter = " + loginCounter + " WHERE UserName = '" + UserName + "'";
            SqlConnection conn = getConnection();
            SqlCommand command = new SqlCommand(UPDATE_COUNTER, conn);
            conn.Open();
            command.ExecuteNonQuery();
            conn.Close();
        }

        private static void BlockUser(string UserName)
        {
            string BLOCK = "UPDATE LOS.dbo.Holders SET Enable = 'False' WHERE UserName = '" + UserName + "'";
            SqlConnection conn = getConnection();
            SqlCommand command = new SqlCommand(BLOCK, conn);
            conn.Open();
            command.ExecuteNonQuery();
            conn.Close();
        }

        public static string GetVersion(string UserName)
        {
            string rtn = null;

            string GET_VER = "SELECT UserName, Version FROM LOS.dbo.Holders WHERE UserName = '" + UserName + "'";
            SqlConnection conn = getConnection();
            SqlCommand command = new SqlCommand(GET_VER, conn);
            SqlDataReader Dr;
            try
            {
                conn.Open();
                Dr = command.ExecuteReader();
                Dr.Read();
                rtn = Dr["Version"].ToString();
                Dr.Close();
            }
            finally
            {
                conn.Close();
            }

            return rtn;

        }

        public static void LogingOut(string UserName)
        {
            string UPD_CUST_VER = "UPDATE LOS.dbo.Holders SET Version = '" + DateTime.Now + "' WHERE UserName = '" + UserName + "'";
            SqlConnection conn = getConnection();
            SqlCommand command = new SqlCommand(UPD_CUST_VER, conn);

            try
            {
                conn.Open();
                command.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }

            DataAccessManager.EditCounter(UserName, 0);

        }

    }
}
